package controllers

import(
	"platform_stato/dao/mysql"
	"github.com/astaxie/beego"
	"github.com/astaxie/beego/logs"
	"github.com/astaxie/beego/orm"
	"strconv"
	"strings"
	"errors"
	"fmt"
	"time"
	"platform_stato/models"
)

const(
	H_NUM =39
) 

type DataController struct{
	Coding
}

type sys_pro struct{
	Id int
	Game_name,Server_type,Db,Ip,Password,Login string
	Port int 
} 

type p_date struct{
	Stime,Etime time.Time
}

var date_s p_date 

func init(){
	//注册database对象
	pros := getPros("*")
	
	for _,pro := range pros{
		pwd := beego.AppConfig.String("password")

		if pro.Password != "" {
			pwd = pro.Password
		}
		user := beego.AppConfig.String("login") 
		if pro.Login != ""{
			user = pro.Login
		}
		orm.RegisterDriver("mysql", orm.DRMySQL)
		orm.RegisterDataBase(pro.Db, "mysql", user+":"+pwd+"@tcp("+pro.Ip+":"+strconv.Itoa(pro.Port)+")/"+pro.Db+"?charset=utf8", 30, 30)
	}
}

func header() [H_NUM]map[string]interface{}{
	//num := 40
	var header [H_NUM]map[string]interface{} 

	for i :=0; i<H_NUM; i++{
		header[i] = make(map[string]interface{}) //malloc
	}  
	key := 0
	header[key]["field"] = "time"
	header[key]["title"] = "时间"
	header[key]["width"] = 80 
	header[key]["sort"] = true
	header[key]["totalRowText"] = "合计"
	key++ 
	header[key]["field"] = "sid"
	header[key]["title"] = "服务器名称"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "boot_login_device"
	header[key]["title"] = "启动设备"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "boot_new_device"
	header[key]["title"] = "新增设备"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "pile_reg_user"
	header[key]["title"] = "全服累计注册"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "reg_users"
	header[key]["title"] = "全服注册账号数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "pay_amount"
	header[key]["title"] = "总充值额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "users_retained_precent"
	header[key]["title"] = "全服账号留存"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "account_login_user"
	header[key]["title"] = "总登录帐号数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "account_pay_user"
	header[key]["title"] = "总付费帐号数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "account_pay_precent"
	header[key]["title"] = "总付费率"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "account_arpu"
	header[key]["title"] = "帐号arpu"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "reg_roles"
	header[key]["title"] = "新增角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "new_pay_users"
	header[key]["title"] = "新增充值角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "new_pay_amount"
	header[key]["title"] = "新增角色充值额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "newPayArpu"
	header[key]["title"] = "新增角色充值arpu"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "newPayPrecent"
	header[key]["title"] = "新增角色付费率"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "pile_reg_roles"
	header[key]["title"] = "累积角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "login_user"
	header[key]["title"] = "登录角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "role_retained_precent"
	header[key]["title"] = "角色留存"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "pay_users"
	header[key]["title"] = "充值角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "arpu"
	header[key]["title"] = "角色arpu"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "pay_precent"
	header[key]["title"] = "角色付费率"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activeUser"
	header[key]["title"] = "活跃帐号数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activePayUsers"
	header[key]["title"] = "活跃充值帐号数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activePayAmount"
	header[key]["title"] = "活跃帐号充值额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activeAccountPayArpu"
	header[key]["title"] = "活跃帐号arpu"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activeAccountPayPrecent"
	header[key]["title"] = "活跃帐号付费率"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "active_user"
	header[key]["title"] = "活跃角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "active_pay_users"
	header[key]["title"] = "活跃充值角色数"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "active_pay_amount"
	header[key]["title"] = "活跃角色充值额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activePayArpu"
	header[key]["title"] = "活跃角色充值arpu"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "activePayPrecent"
	header[key]["title"] = "活跃角色付费率"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "first_pay_users"
	header[key]["title"] = "首充充值角色"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "first_pay_amount"
	header[key]["title"] = "角色首充额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "more_pay_users"
	header[key]["title"] = "角色后续充值账号"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "more_pay_amount"
	header[key]["title"] = "角色后续充值额"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "online_num"
	header[key]["title"] = "当前在线"
	header[key]["width"] = 50
	key++ 
	header[key]["field"] = "max_online"
	header[key]["title"] = "最高在线"
	header[key]["width"] = 50

	return header
}

func (this *DataController) Index(){
	
	if isAjax := this.Ctx.Input.IsAjax(); isAjax{
		date := this.GetString("date")
		
		errs := setTime(date)
		fmt.Println(errs);
		if errs != nil {
			this.Faild(-1,"未选择时间")
		}
		
		pro_id,_ := this.GetInt("pro_id")

		var pro = new(sys_pro)
		
		o :=mysql.GetDbObj()
		err := o.Raw("SELECT * FROM sys_pro WHERE id =?",pro_id).QueryRow(&pro)

		if err != nil {
			this.Faild(-1,"查询不到此项目")
		}else {
			fmt.Println(pro)
			cData,err := getDeviceInfo(pro.Db)
			if err != nil{
				this.Faild(-1,err)
			}

			mData,err := getMainInfo(pro.Db)
			if err != nil{
				this.Faild(-1,err)
			}

			rData,err := getUserRetained(pro.Db)

			if err != nil{
				this.Faild(-1,err)
			}
			
			for key,param := range mData{
				for _,c := range cData{
					if param["time"] == c.Time{
						mData[key]["boot_new_device"] = c.Boot_new_device
						mData[key]["boot_login_device"] = c.Boot_login_device
					}
				}
				
				for _,r := range rData{
					if param["time"] == r.Time{
						mData[key]["users_retained_precent"] = r.Retained
					}
				}
			}
			
			d := make(map[string]interface{},2)
			d["Header"] =header()
			d["Param"] =mData 
			this.Suc(d)
		}	

	}else{
		pros := getPros("id,game_name,server_type")
		this.Data["Pros"] = pros
		this.TplName = "data.tpl"
	}
	
}

func getPros(field string) []sys_pro{
	var pros []sys_pro
	o :=mysql.GetDbObj()
	_, err := o.Raw("SELECT "+field+" FROM sys_pro").QueryRows(&pros)

	if err != nil{
		data :=time.Now().Format("2006-01-02") 
		file := data+"error.log"
		logs.SetLogger(logs.AdapterFile,`{"filename":"`+file+`"}`)
		logs.Info(err)
	}
	return pros
}

func getDeviceInfo(db string) ([]models.DateDataChannel,error){

	qb, _ := orm.NewQueryBuilder("mysql")
	qb.Select("FROM_UNIXTIME(created,'%Y-%m-%d') as `time`, boot_new_device, boot_login_device").
		From("day_data_channel").
		Where("created BETWEEN ?  AND ? AND channel_id=0")	
	sql := qb.String()
	
	var data []models.DateDataChannel

	o := orm.NewOrm()
	o.Using(db)	
	
	_,err := o.Raw(sql,date_s.Stime.Unix(),date_s.Etime.Unix()).QueryRows(&data)
	if err != nil{
		return nil,err
	}

	return data,nil
}

func getUserRetained(db string) ([]models.UserRetained,error){
	qb, _ := orm.NewQueryBuilder("mysql")
	qb.Select("FROM_UNIXTIME(created+86400,'%Y-%m-%d') as `time`, IF(day_1>-1, CONCAT(round(day_1/new_num*100,2),'%'),'') as retained").
		From("users_retained").
		Where("created BETWEEN ?  AND ? AND channel=0")	
	sql := qb.String()
	var data []models.UserRetained

	o := orm.NewOrm()
	o.Using(db)	
	
	_,err := o.Raw(sql,date_s.Stime.Unix()-86400,date_s.Etime.Unix()-86400).QueryRows(&data)
	if err != nil{
		return nil,err
	}

	return data,nil
}

func getMainInfo(db string) ([]orm.Params,error){

	/*$sql = "SELECT a.*, 
			b.new_num reg_users, 
			c.new_num new_device, 
			d.pile_reg_user,d.account_login_user,d.account_pay_user,d.activeUser,d.activePayUsers,d.activePayAmount,
			FROM_UNIXTIME(a.created, '%Y-%m-%d') `time` 
			FROM realtime_dayinfo_statistic a 
			LEFT JOIN users_retained b 
			ON a.created=b.created 
			LEFT JOIN device_retained c 
			ON a.created=c.created AND b.channel=c.channel 
			LEFT JOIN realtime_dayinfo_statistic d 
			ON a.created=d.created 
			WHERE a.created BETWEEN 
			{$this->start_time} AND {$this->end_time} AND a.sid={$this->server_id} AND b.channel=0 AND d.sid=0 
			ORDER BY created DESC";
	*/

	qb,_ := orm.NewQueryBuilder("mysql")
	qb.Select("FROM_UNIXTIME(a.created,'%Y-%m-%d') as `time`,a.*,b.new_num reg_users,c.new_num as new_device,"+
			"ROUND(a.active_pay_amount/a.active_pay_users,2) as activePayArpu,CONCAT(ROUND(a.active_pay_users/a.active_user*100,2),'%') as activePayPrecent,"+
			"ROUND(a.new_pay_amount/a.new_pay_users,2) as newPayArpu, CONCAT(ROUND(a.new_pay_users/a.reg_roles*100,2),'%') as newPayPrecent,"+
			"ROUND(a.activePayAmount/a.activePayUsers,2) as activeAccountPayArpu,CONCAT(ROUND(a.activePayUsers/a.activeUser*100,2),'%') as activeAccountPayPrecent,"+
			"ROUND(a.pay_amount/a.account_pay_user,2) as account_arpu, CONCAT(ROUND(a.account_pay_user/a.account_login_user*100,2),'%') as account_pay_precent").
		From("realtime_dayinfo_statistic as a").
		LeftJoin("users_retained as b").On("a.created=b.created").
		LeftJoin("device_retained c").On("a.created=c.created AND b.channel=c.channel"). 
		Where("a.created BETWEEN ? AND ? AND b.channel=0 AND a.sid=0").
		OrderBy("a.created").Desc()
	sql := qb.String()
	
	var data []orm.Params

	o := orm.NewOrm()
	o.Using(db)	

	_,err :=o.Raw(sql,date_s.Stime.Unix(),date_s.Etime.Unix()).Values(&data)
	
	if err != nil{
		return nil,err
	}
	return data,nil
}


func setTime(date string) error{
	times := strings.Split(date, " - ")	
	if len(times)!=2{
		return errors.New("时间格式不正确")
	}
	loc, _ := time.LoadLocation("Local")
	timeLayout := "2006-01-02 15:04:05"             
	
	date_s.Stime, _ = time.ParseInLocation(timeLayout,times[0]+" 00:00:00", loc)
	date_s.Etime, _ = time.ParseInLocation(timeLayout,times[1]+" 23:59:59", loc)
	return nil
}